/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package wikitrafic.iovanalex.ro;

import java.sql.*;
import java.util.ArrayList;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.UUID;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Administrator
 */
public class DbComm {

    private TraceLog log;

    public DbComm(TraceLog log) {
        this.log = log;
    }

    public void store() {
        Connection conn = getConnection();
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DbComm.class.getName()).log(Level.SEVERE, null, ex);
        }


        ArrayList<TraceItem> traces = log.getTraces();
        System.out.print(traces.size());

        //ArrayList<TraceItem> ti[]= traces.toArray(TraceItem);


        int noOfRec = traces.size();
        int startTime = traces.get(0).getTimestamp();
        int endTime = traces.get(noOfRec - 1).getTimestamp();
        int uid=log.getUid();
        String tempid=UUID.randomUUID().toString();
        String logid=tempid.substring(tempid.length()-12, tempid.length());
        //BEGIN OF TRANSACTION

        this.storeLogData(startTime, endTime, noOfRec,uid,logid);

        for (int i = 1; i < noOfRec; i++) {
            TraceItem previousItem = traces.get(i - 1);
            TraceItem currentItem = traces.get(i);
            try {
                //first stores the two points forming the line segment, if it does not exist
                
                tempid=UUID.randomUUID().toString();
                String firstId=tempid.substring(tempid.length()-12, tempid.length());
                tempid=UUID.randomUUID().toString();
                String secondId=tempid.substring(tempid.length()-12, tempid.length());

                double roundlat=DbComm.Round(previousItem.getPoint().getLat(), 6);
                double roundlon=DbComm.Round(previousItem.getPoint().getLon(), 6);

                //String query="INSERT IGNORE INTO Points (pointId, lat,lon) VALUES (\'"+firstId+"\', "+roundlat+", "+roundlon+")";
                String query="CALL StorePoint (\'"+firstId+"\', "+roundlat+", "+roundlon+")";
                //System.out.println(query);
                stmt.executeUpdate(query);

                roundlat=DbComm.Round(currentItem.getPoint().getLat(), 6);
                roundlon=DbComm.Round(currentItem.getPoint().getLon(), 6);
                //query="INSERT IGNORE INTO Points (pointId, lat,lon) VALUES (\'"+secondId+"\', "+roundlat+", "+roundlon+")";
                query="CALL StorePoint (\'"+secondId+"\', "+roundlat+", "+roundlon+")";
                //System.out.println(query);
                stmt.executeUpdate(query);

                //now we have to store the line formed by the tow points from above
                tempid=UUID.randomUUID().toString();
                String lineId=tempid.substring(tempid.length()-12, tempid.length());
                GPSPoint p1=previousItem.getPoint();
                GPSPoint p2=currentItem.getPoint();
                double length = p1.getDistanceInMeters(p2);
                //System.out.println("Calling stored procedure"+length);
                query="CALL StoreLine (\'"+lineId+"\', \'"+firstId+"\', \'"+secondId+"\', "+length+")";
                stmt.executeUpdate(query);

                //after all that we have to add one "trace" linking-it to the previous data
                tempid=UUID.randomUUID().toString();
                String traceId=tempid.substring(tempid.length()-12, tempid.length());
                int timestamp=currentItem.getTimestamp();
                double speed=(length*1000.0)/(1.0/360.0);
                query="CALL StoreTrace (\'"+traceId+"\', \'"+lineId+"\', "+timestamp+", "+speed+", \'"+logid+"\')";
                //System.out.println(query);
                stmt.executeUpdate(query);
                
            } catch (SQLException ex) {
                Logger.getLogger(DbComm.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
        //END OF TRANSACTION
    }

    private void storeLogData(int startTime, int endTime, int noOfRecs, int uid, String logId) {    //called only once for storing the metadata about current log;
        Connection conn = getConnection();
        Statement stmt = null;
        
        try {
            stmt = conn.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(DbComm.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            //stores the common data for one log
            String query="INSERT INTO Logs (logid, startTime, endTime, noOfRec,uid) VALUES (\'"
                    +logId+"\', "+startTime+" ,"+endTime+", "+noOfRecs+", "+uid+")";
            System.out.println(query);
            stmt.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(DbComm.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private Connection getConnection() {

        ConfUtil cu = new ConfUtil("wikitrafic.properties");
        String dbhost = cu.getProperty("dbhost");
        String dbport = cu.getProperty("dbport");
        String dbuser = cu.getProperty("dbuser");
        String dbpass = cu.getProperty("dbpass");
        String dbname = cu.getProperty("dbname");

        try {
            Class.forName("org.postgresql.Driver");
        } catch (ClassNotFoundException cnfe) {
            System.out.println("Couldn't find the driver!");
            cnfe.printStackTrace();
            System.exit(1);
        }
        Connection c = null;
        try {
            // The second and third arguments are the username and password,
            // respectively. They should be whatever is necessary to connect
            // to the database.
            c = DriverManager.getConnection("jdbc:mysql://" + dbhost + "/" + dbname, dbuser, dbpass);
        } catch (SQLException se) {
            System.out.println("Couldn't connect: print out a stack trace and exit.");
            se.printStackTrace();
            System.exit(1);
        }
        if (c != null) {
            System.out.println("Hooray! We connected to the database!");
        } else {
            System.out.println("We should never get here.");
        }
        return c;
    }
     private static double Round(double Rval, int Rpl) {
        double p = (double) Math.pow(10, Rpl);
        Rval = Rval * p;
        double tmp = Math.round(Rval);
        return (double) tmp / p;
    }

}
